﻿Imports System.Activities
Imports System.Data.SqlClient
Imports System.Activities.Presentation.Metadata
Imports System.ComponentModel
Imports System.Drawing

Public Class GetIndexFragmentationActivity
    Inherits MSSQLBaseActivity

    Public Property TableName As InArgument(Of String)

    Public Sub New()
        Me.DisplayName = My.Resources.GetIndexFragmentationActivityDisplayName
    End Sub

    Protected Overrides Function BeginExecute(context As System.Activities.AsyncCodeActivityContext, callback As System.AsyncCallback, state As Object) As System.IAsyncResult
        Dim tableName = context.GetValue(Me.TableName)
        Dim conn = context.GetValue(Me.ConnectionStrings)
        Dim useLog = context.GetValue(Me.OutSQLLog)
        Dim asyncExecute = New asyncExecuteDelegate(AddressOf ExecuteSQL)
        context.UserState = asyncExecute

        Return asyncExecute.BeginInvoke(CreateSQLStrings(tableName), conn, True, useLog, callback, state)
    End Function

    Public Overrides Sub Register()
        MyBase.Register()

        Dim build As New AttributeTableBuilder
        'ExecuteApplicationActivity Activity
        build.AddCustomAttributes(GetType(GetIndexFragmentationActivity),
                                              New ToolboxBitmapAttribute(GetType(GetIndexFragmentationActivity), "GetIndexFragment.png"))
        build.AddCustomAttributes(GetType(ReOrganizeIndexActivity), "TableName",
                                              New CategoryAttribute(My.Resources.CategoryDetail),
                                              New DisplayNameAttribute(My.Resources.TableNameDisplay),
                                              New DescriptionAttribute(My.Resources.TableNameDescription))

        MetadataStore.AddAttributeTable(build.CreateTable)
    End Sub

    ''' <summary>実行する SQL の生成</summary>
    ''' <remarks>http://msdn.microsoft.com/ja-jp/library/ms189858(SQL.90).aspx</remarks>
    Private Function CreateSQLStrings(ByVal tableName As String) As String
        Dim sqlBuilder As New System.Text.StringBuilder
        With sqlBuilder
            .Append("SELECT ")
            .Append("  A.INDEX_ID, ")
            .Append("  C.NAME AS TABLENAME, ")
            .Append("  B.NAME, ")
            .Append("  A.AVG_FRAGMENTATION_IN_PERCENT ")
            .Append("FROM ")
            .Append("  SYS.DM_DB_INDEX_PHYSICAL_STATS (")
            .Append("    DB_ID(), ")
            If tableName.Trim = "" Then
                .Append("    NULL,")
            Else
                .Append("    OBJECT_ID(N'" + tableName + "'),")
            End If
            .Append("    NULL, ")
            .Append("    NULL, ")
            .Append("    NULL) AS A")
            .Append("    INNER JOIN SYS.INDEXES AS B ")
            .Append("      ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID")
            .Append("    INNER JOIN SYS.TABLES AS C ")
            .Append("      ON A.OBJECT_ID = C.OBJECT_ID ")
            .Append(";")
        End With
        Return sqlBuilder.ToString
    End Function

End Class
